package dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Contracheque;

class ContrachequeDAO implements IContrachequeDAO {

	@Override
	public boolean insert(Contracheque c) {
		String sql = "insert into contracheque (mes_referencia, ano_referencia, dias_trabalhados, salario, salario_familia,"
				+ " inss, fgts, irrf, pessoa_id_pessoa, id_empresa) values (?,?,?,?,?,?,?,?,?,?);";
		Connection conn = ConnectionManneger.getConnection();
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, c.getMes_referencia() );
			ps.setObject(2, c.getAno_referencia() );
			ps.setObject(3, c.getDias_trabalhados() );
			ps.setObject(4, c.getSalario() );
			ps.setObject(5, c.getSalario_familia() );
			ps.setObject(6, c.getInss() );
			ps.setObject(7, c.getFgts() );
			ps.setObject(8, c.getIrrf() );
			ps.setObject(9, c.getPessoa_id_pessoa() );
			ps.setObject(10, c.getId_empresa() );

			return ps.executeUpdate() > 0;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return false;
	}

	@Override
	public boolean update(Contracheque c) {
		String sql = "update contracheque set mes_referencia = ?, ano_referencia = ?, dias_trabalhados = ?, salario = ?, salario_familia = ?,"
				+ " inss = ? , fgts = ?, irrf = ?, pessoa_id_pessoa = ?, id_empresa = ? where id_contracheque = ?";
		Connection conn = ConnectionManneger.getConnection();
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, c.getMes_referencia() );
			ps.setObject(2, c.getAno_referencia() );
			ps.setObject(3, c.getDias_trabalhados() );
			ps.setObject(4, c.getSalario() );
			ps.setObject(5, c.getSalario_familia() );
			ps.setObject(6, c.getInss() );
			ps.setObject(7, c.getFgts() );
			ps.setObject(8, c.getIrrf() );
			ps.setObject(9, c.getPessoa_id_pessoa() );
			ps.setObject(10, c.getId_empresa() );
			ps.setObject(11, c.getId_contracheque() );

			return ps.executeUpdate() > 0;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return false;
	}

	@Override
	public boolean delete(Contracheque c) {
		String sql = "delete from contracheque where idcontracheque = ?";
		Connection conn = ConnectionManneger.getConnection();
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, c.getId_contracheque() );
			
			return ps.executeUpdate() > 0;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return false;
	}

	@Override
	public Contracheque findById(int id) {
		String sql = "select id_contracheque, mes_referencia, ano_referencia, dias_trabalhados, salario, salario_familia, inss, fgts, irrf,"
				+ " pessoa_id_pessoa, id_empresa from contracheque where id_contracheque = ?";
		Connection conn = ConnectionManneger.getConnection();
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, id );
			
			ResultSet rs = ps.executeQuery();
			
			return rs.next() ? 
					new Contracheque(rs.getInt("id_contracheque"),
								rs.getString("mes_referencia"),
								rs.getString("ano_referencia"),
								rs.getInt("dias_trabalhados"),
								rs.getDouble("salario"),
								rs.getDouble("salario_familia"),
								rs.getDouble("inss"),
								rs.getDouble("fgts"),
								rs.getDouble("irrf"),
								rs.getInt("pessoa_id_pessoa"),
								rs.getInt("id_empresa")) : null;
					
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return null;
	}

	@Override
	public List<Contracheque> findAll() {
		List<Contracheque> contracheques = new ArrayList<Contracheque>();
		String sql = "select id_contracheque, mes_referencia, ano_referencia, dias_trabalhados, salario, salario_familia, inss, fgts, irrf,"
				+ " pessoa_id_pessoa, id_empresa from contracheque";
		Connection conn = ConnectionManneger.getConnection();
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			
			ResultSet rs = ps.executeQuery();
			
			while ( rs.next() ) {
				contracheques.add(					
						new Contracheque(rs.getInt("id_contracheque"),
								rs.getString("mes_referencia"),
								rs.getString("ano_referencia"),
								rs.getInt("dias_trabalhados"),
								rs.getDouble("salario"),
								rs.getDouble("salario_familia"),
								rs.getDouble("inss"),
								rs.getDouble("fgts"),
								rs.getDouble("irrf"),
								rs.getInt("pessoa_id_pessoa"),
								rs.getInt("id_empresa"))
						);
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return contracheques;
	}

	@Override
	public boolean existeRegistro(int id) {
		String sql = "select count(*) as qtd from contracheque where id_contracheque = ?";
		Connection conn = ConnectionManneger.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, id );
			
			ResultSet rs = ps.executeQuery();
			
			rs.next();
			int total = rs.getInt("qtd");
			return total > 0;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return false;
	}

	@Override
	public boolean insertUpdate(Contracheque c) {
		if(existeRegistro(c.getMes_referencia(), c.getAno_referencia(), c.getPessoa_id_pessoa())){
			return update(c);
		}
		else{
			return insert(c);
		}
	}

	@Override
	public List<Contracheque> findAllContrachequeByEmpresa(int id_empresa) {
		List<Contracheque> contracheques = new ArrayList<Contracheque>();
		String sql = "select c.id_contracheque, c.mes_referencia, c.ano_referencia, c.dias_trabalhados, c.salario, c.salario_familia,"
				+ " c.inss, c.fgts, c.irrf, c.pessoa_id_pessoa, c.id_empresa, p.nome_pessoa as nome_funcionario, p.cpf, p.rg"
				+ "  from contracheque c "
				+ " inner join pessoa p on c.pessoa_id_pessoa = p.id_pessoa"
				+ " where id_empresa = ?";
		Connection conn = ConnectionManneger.getConnection();
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, id_empresa);
			
			ResultSet rs = ps.executeQuery();
			
			while ( rs.next() ) {
				contracheques.add(					
						new Contracheque(rs.getInt("id_contracheque"),
								rs.getString("mes_referencia"),
								rs.getString("ano_referencia"),
								rs.getInt("dias_trabalhados"),
								rs.getDouble("salario"),
								rs.getDouble("salario_familia"),
								rs.getDouble("inss"),
								rs.getDouble("fgts"),
								rs.getDouble("irrf"),
								rs.getInt("pessoa_id_pessoa"),
								rs.getInt("id_empresa"),
								rs.getString("rg"),
								rs.getString("cpf"),
								rs.getString("nome_funcionario"))
						);
			
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return contracheques;
	}

	@Override
	public List<Contracheque> findAllContrachequeByEmpresaMesAno(int id_empresa, String mesRef, String anoRef) {
		List<Contracheque> contracheques = new ArrayList<Contracheque>();
		String sql = "select c.id_contracheque, c.mes_referencia, c.ano_referencia, c.dias_trabalhados, c.salario, c.salario_familia,"
				+ " c.inss, c.fgts, c.irrf, c.pessoa_id_pessoa, c.id_empresa, p.nome_pessoa as nome_funcionario, p.cpf, p.rg"
				+ "  from contracheque c "
				+ " inner join pessoa p on c.pessoa_id_pessoa = p.id_pessoa"
				+ " where id_empresa = ? and mes_referencia = ? and ano_referencia = ?";
		Connection conn = ConnectionManneger.getConnection();
		
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, id_empresa);
			ps.setObject(2, mesRef);
			ps.setObject(3, anoRef);
			
			ResultSet rs = ps.executeQuery();
			
			while ( rs.next() ) {
				contracheques.add(					
						new Contracheque(rs.getInt("id_contracheque"),
								rs.getString("mes_referencia"),
								rs.getString("ano_referencia"),
								rs.getInt("dias_trabalhados"),
								rs.getDouble("salario"),
								rs.getDouble("salario_familia"),
								rs.getDouble("inss"),
								rs.getDouble("fgts"),
								rs.getDouble("irrf"),
								rs.getInt("pessoa_id_pessoa"),
								rs.getInt("id_empresa"),
								rs.getString("rg"),
								rs.getString("cpf"),
								rs.getString("nome_funcionario"))
						);
			
			} 
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return contracheques;
	}
	
	@Override
	public boolean existeRegistro(String mes, String ano, int id_pessoa) {
		String sql = "select count(*) as qtd from contracheque where mes_referencia = ? and ano_referencia = ? and pessoa_id_pessoa = ?";
		Connection conn = ConnectionManneger.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setObject(1, mes );
			ps.setObject(2, ano );
			ps.setObject(3, id_pessoa );
			
			ResultSet rs = ps.executeQuery();
			
			rs.next();
			int total = rs.getInt("qtd");
			return total > 0;
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionManneger.closeConnection();
		}
		return false;
	}
}